N-of-N

The N-of-N functionality allows you to perform a series of nested loop queries (iterations) across multiple hierarchies.

This is a powerful tool used to answer classic analytical problems like "finding the top regions, and then for each region finding the top 5 products sold in each region". Pyramid includes an N-of-N engine because it is extremely difficult (near impossible) to generate the logic for this iterative query type using standard filters, calculations and sorts.

ClosedExample of how N-of-N is Useful

Assume you had a listing of margin rates by every manufacturer (11 in the sample demo) by every promotion run (16). As partiality shown below.

From the above query, you'd like to find the top 3 manufacturers and the top 2 promotions for each of these manufacturers based on margin. Most analysts instinctively assume to find the top 6 manufacturer-promotion items using a simple filter. However, this will not produce the right outcome. As shown below, this produces the top 6 'combo' items from the entire list.

What you really want is the following result instead: the top 3 manufacturers, and then for each one, their top 2 promotions.

 

Note: This feature is not available in the Community Edition.

The N-of-N wizard generates visual results only and does NOT generate any custom elements. If you wish to save N-of-N results, you can create a separate report for each N-of-N generation and then save it to ensure your results are preserved for future reference.

Adding N-of-N logic to your Report

To use N-of-N filtering the query must contain at least two hierarchies (and generally one measure). Then you can either use the "quick" N-of-N to apply default settings to your existing query or use the more advanced N-of-N wizard to configure and customize the logic. When you run the query, the series of nested loops are performed in succession, where each loop inherits the output data from the previous loop. Each loop is associated with a single row hierarchy and a single loop filter.

Visualizations

N-of-N is usually used in grid visualizations. However, there is no limitation on is application and it can be used on all visuals. Once you've configured an N-of-N filter, you can move the hierarchy chips to other drop zones in order to display the query results in a different way. For example, you can move one of the hierarchies to the Color zone

Quick N-of-N

The easiest way to add N-of-N logic is via the "quick" menu option from the drop zones. The option will be enabled if there are 2 or more 'blue' hierarchy chips in the zone. It will then apply a simple 'top 5 of the top 5' to the hierarchies using the currently deployed measure. If you then want to change these settings, you can open up the N-of-N wizard dialog (see below).

See the examples below for a step-by-step tutorial on how to use the Quick N-fo-N.

N-of-N Wizard

The N-of-N wizard dialog is available from the Query ribbon, in the 'functions' group. Clicking the button will allow you to add new, edit existing and remove N-of-N logic from your reports and queries. (This is how you can edit or refine the logic created from the quick menu item described above).

Te following panels are presented in the wizard dialog (more detail can be seen in the examples below):

Targets

In this panel select the hierarchies and combination of hierarchies that will be used to drive the selections. From this panel you can also elect to drive more than one sequence of N-of-N logic (click the blue plus sign to add each sequence). Note that:

  • Logic can be set across 2 or more hierarchies per sequence (i.e. N-of-N-of-N....).
  • Each hierarchy can be used in one sequence only

Settings

In the next panel, configure the filter settings for the selected hierarchy to drive the way the logic will be applied. They will control how the "n" elements for the chosen hierarchy will be materialized and used in the rest of the N-of-N iterative sequence.

A key aspect of the filtering definition used in the settings relates to the data point (or tuple) that drives the mathematical operations on the filter itself. This is set and defined using the hierarchy trees and subsequently picking specific elements from those trees.

  • For more details on how to use and configure settings see the filter dialog, which has identical options. This includes using parameterization to offer runtime changes to the settings.

Examples

The following examples will demonstrate how to use N-of-N and the different interfaces.

ClosedExample: Quick N-of-N
STEP 1

Add at least two hierarchies to the query. If you're working with grids, place the hierarchies in either Columns or Rows. If you're working with charts, place them in one of the Trellis zones. Click the drop zone's context menu and select N-of-N.

STEP 2

The N-of-N filter will be applied, and by default will filter each element by a top count of 5.

STEP 3

To edit the N-of-N filter, click the context menu and select Edit to open the N-of-N Wizard.

ClosedExample: Using the N-of-N Wizard
STEP 1

Start by ensuring that you have at least two hierarchies in the query. The N-of-N function will be only enabled once you've added two hierarchies to the query.

In this example, there are three hierarchies: Year, Manufacturer, and Product Sub Category. Open the N-of-N wizard from the Query ribbon.

STEP 2

In the first column, click the plus sign to add N-of-N targets (the hierarchies that you want to apply the N-of-N filter to).

STEP 3

Click the Filter Targets drop down and select the hierarchies you want to filter.

STEP 4

For each target, you must configure the N-of-N settings in the next panel, labeled N-of-N Settings. Select a target and configure its filter:

  • Type: select the filter type from the drop down.
  • Elements: select or deselect. Choose Select to include the filtered values in the query; choose Deselect to exclude them from the query.
  • Fixed Value: enter a static value
  • Parameter: base the filter on a new quick parameter or an existing parameter

Then, select the hierarchies and/or measure according to which the target should be filtered.

In this example, a top count of 5, by sales is configured for the Manufacturer hierarchy, and a top count of 2, by sales for the Product Sub Category hierarchy.

STEP 5

Click Apply to apply the N-of-N filters to the query. In the example, the query now shows the top 5 manufacturers for each year. For each of the top 5 manufacturers, the top 2 product sub categories is shown: